package com.citytransport.prokopjuk.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import org.apache.log4j.Logger;

import com.citytransport.prokopjuk.connectDB.ConnectionPool;
import com.citytransport.prokopjuk.vehicleDTO.Vehicle;

public class DAOVehicle implements IDAOVehicle {
    private ConnectionPool pool;
    static Logger logger = Logger.getLogger(DAOVehicle.class.getName());

    public DAOVehicle() {
	pool = ConnectionPool.getConnectionPool();
    }

    public void create(Vehicle vehicle) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con.prepareCall("INSERT INTO VEHICLE VALUES(?,?,?,?,?)");
	    prepStmt.setInt(1, vehicle.getID());
	    prepStmt.setInt(2, vehicle.getSeatAmount());
	    prepStmt.setString(3, vehicle.getRegistrationNumber());
	    prepStmt.setString(4, vehicle.getModel());
	    prepStmt.setInt(5, vehicle.getRouteID());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
    }

    public void delete(Vehicle vehicle) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;

	try {
	    prepStmt = con
		    .prepareStatement("delete from vehicle where vehicleID = ?");
	    prepStmt.setInt(1, vehicle.getID());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}

    }

    public HashMap<String, String> read(int vehicleID) {
	Connection con = pool.getConnection();
	HashMap<String, String> rez = new HashMap<String, String>();
	try {
	    PreparedStatement prepStmt = con
		    .prepareStatement("select * from vehicle where vehicleID = ?");
	    prepStmt.setInt(1, vehicleID);
	    ResultSet set = prepStmt.executeQuery();
	    set.next();
	    rez.put("vehicleID", set.getString("vehicleID"));
	    rez.put("seatAmount", new Integer(set.getInt("seatAmount"))
		    .toString());
	    rez.put("registrationNumber", set.getString("REGISTRATIONNUMBER"));
	    rez.put("model", set.getString("model"));
	    rez.put("routeID", new Integer(set.getInt("routeID")).toString());
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}
	return rez;
    }

    public void update(Vehicle vehicle,  int seatAmount,
	    String registrationNumber, String model, int routeID) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con.prepareStatement("update vehicle set seatAmount = ?, registrationNumber = ?," +
	    		"model = ?, routeID = ? where vehicleID = ?");
	    prepStmt.setInt(1, seatAmount);
	    prepStmt.setString(2, registrationNumber);
	    prepStmt.setString(3, model);
	    prepStmt.setInt(4, routeID);
	    prepStmt.setInt(5, vehicle.getID());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}
    }

    public void delete(int vehicleID) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con.prepareStatement("delete from vehicle where vehicleID = ?");
	    prepStmt.setInt(1, vehicleID);
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}	
    }

    public HashMap<String, String> readAtRoute(int routeID) {
	Connection con = pool.getConnection();
	HashMap<String, String> rez = new HashMap<String, String>();
	try {
	    PreparedStatement prepStmt = con
		    .prepareStatement("select * from vehicle where routeID = ?");
	    prepStmt.setInt(1, routeID);
	    ResultSet set = prepStmt.executeQuery();
	    set.next();
	    rez.put("vehicleID", set.getString("vehicleID"));
	    rez.put("seatAmount", new Integer(set.getInt("seatAmount"))
		    .toString());
	    rez.put("registrationNumber", set.getString("REGISTRATIONNUMBER"));
	    rez.put("model", set.getString("model"));
	    rez.put("routeID", new Integer(set.getInt("routeID")).toString());
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}
	return rez;	
    }
}
